/*CREACION DE LA BASE DE DATOS*/
CREATE DATABASE [Testeitor] ON  PRIMARY 
( NAME = N'Testeitor', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testeitor.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
 LOG ON 
( NAME = N'Testeitor_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Testeitor_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [Testeitor] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE Testeitor
GO
/*CREACION DE LAS TABLAS*/
CREATE TABLE Proyecto
(
	id int identity not null,
	nombre nvarchar(20) not null,
	fechaInicio datetime not null,
	fechaFin datetime null,
	descripcion nvarchar(200) null,
	CONSTRAINT PK_proyecto PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE PlanTest
(
	id int identity not null,
	proyecto_id int not null,
	fechaCreacion datetime not null,
	fechaFinEstimada datetime not null,
	fechaFinReal datetime null,
	descripcion nvarchar(200) null,
	autor_id int not null,
	estado_id int not null,
	CONSTRAINT PK_planTest PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE SuiteXPlan
(
	id int identity not null,
	plan_id int not null,
	suite_id int not null,
	CONSTRAINT PK_suiteXPlan PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Suite
(
	id int identity not null,
	detalle nvarchar(200) null,
	fechaCreacion datetime not null,
	tags nvarchar(50) null,
	estado_id int not null,
	autor_id int not null,
	CONSTRAINT PK_suite PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE TestXSuite
(
	id int identity not null,
	test_id int not null,	
	suite_id int not null,
	CONSTRAINT PK_testXSuite PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Test
(
	id int identity not null,
	titulo nvarchar(20) null,
	descripcion nvarchar(200) null,
	fechaCreacion datetime not null,
	autor_id int not null,
	tester_id int not null,
	prioridad int null,
	estado_id int not null, 
	pasos nvarchar(200) null,
	resultados_esperados nvarchar(200) null,
	casoDeUso_id int not null,
	CONSTRAINT PK_test PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Asignacion
(
	id int identity not null,
	tiempoLimite datetime not null,
	estado_id int not null,
	usuario_id int not null,
	test_id int not null,
	fechaAsignacion datetime not null,
	CONSTRAINT PK_asignacion PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE ResultadoTest
(
	id int identity not null,
	descripcion nvarchar(200) not null,
	test_id int not null,
	checked bit null,
	warnings nvarchar(200) null,
	errores nvarchar(200) null,
	fechaEjecucion datetime not null,
	estado_id int not null,
	CONSTRAINT PK_resultadoTest PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Estado
(
	id int identity not null,
	entidad nvarchar(50) null,
	descripcion nvarchar(200) null,
	CONSTRAINT PK_estado PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Usuario
(
	id int identity not null,
	nombre nvarchar(20) not null,
	apellido nvarchar(20) not null,
	fechaAlta datetime not null,
	rol_id int not null,
	login nvarchar(20) not null,
	password nvarchar(20) not null,
	email nvarchar(20) null,
	CONSTRAINT PK_usuario PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Rol
(
	id int identity not null,
	nombre nvarchar(20) not null,
	descripcion nvarchar(200) null,
	CONSTRAINT PK_rol PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE CasoDeUso
(
	id int identity not null,
	numero int not null,
	titulo nvarchar(20) null,
	objetivo nvarchar(100) null,
	descripcion nvarchar(200) null,
	autor nvarchar(20) null,
	proyecto_id int not null,
	url nvarchar(100) null,
	CONSTRAINT PK_casoDeUso PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Permiso
(
	id int identity not null,
	formulario_id int not null,
	alta int null,
	baja int null,
	modificacion int null,
	consulta int null,
	CONSTRAINT Pk_permiso PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE PermisoXRol
(
	id int identity not null,
	permiso_id int not null,
	rol_id int not null,
	CONSTRAINT PK_permisoXRol PRIMARY KEY CLUSTERED (id ASC)
)
GO

CREATE TABLE Formulario
(
	id int identity not null,
	nombre nvarchar(200) null,
	CONSTRAINT PF_formulario PRIMARY KEY CLUSTERED (id ASC)
)
GO

/*CREACION DE LOS CONSTRAINTS*/
ALTER TABLE PlanTest WITH CHECK
ADD CONSTRAINT FK_PlanTest_proyecto FOREIGN KEY(proyecto_id)
REFERENCES Proyecto(id)
GO

ALTER TABLE PlanTest WITH CHECK
ADD CONSTRAINT FK_PlanTest_usuario FOREIGN KEY(autor_id)
REFERENCES Usuario(id)
GO

ALTER TABLE PlanTest WITH CHECK
ADD CONSTRAINT FK_PlanTest_estado FOREIGN KEY(estado_id)
REFERENCES Estado(id)
GO

ALTER TABLE SuiteXPlan WITH CHECK
ADD CONSTRAINT FK_SuiteXPlan_plan FOREIGN KEY(plan_id) 
REFERENCES PlanTest(id)
GO

ALTER TABLE SuiteXPlan WITH CHECK
ADD CONSTRAINT FK_SuiteXPlan_suite FOREIGN KEY(suite_id) 
REFERENCES Suite(id)
GO

ALTER TABLE Suite WITH CHECK
ADD CONSTRAINT FK_Suite_autor FOREIGN KEY(autor_id)
REFERENCES Usuario(id)
GO

ALTER TABLE Suite WITH CHECK
ADD CONSTRAINT FK_Suite_estado FOREIGN KEY(estado_id)
REFERENCES Estado(id)
GO

ALTER TABLE TestXSuite WITH CHECK
ADD CONSTRAINT FK_TestXSuite_suite FOREIGN KEY(suite_id)
REFERENCES Suite(id)
GO

ALTER TABLE TestXSuite WITH CHECK
ADD CONSTRAINT FK_TestXSuite_test FOREIGN KEY(test_id)
REFERENCES Test(id)
GO

ALTER TABLE Test WITH CHECK
ADD CONSTRAINT FK_Test_estado FOREIGN KEY(estado_id)
REFERENCES Estado(id)
GO

ALTER TABLE Test WITH CHECK
ADD CONSTRAINT FK_Test_autor FOREIGN KEY(autor_id)
REFERENCES Usuario(id)
GO

ALTER TABLE Test WITH CHECK
ADD CONSTRAINT FK_Test_tester FOREIGN KEY(tester_id)
REFERENCES Usuario(id)
GO

ALTER TABLE Test WITH CHECK
ADD CONSTRAINT FK_Test_casoDeUso FOREIGN KEY(casoDeUso_id)
REFERENCES CasoDeUso(id)
GO

ALTER TABLE Asignacion WITH CHECK
ADD CONSTRAINT FK_Asignacion_test FOREIGN KEY(test_id)
REFERENCES Test(id)
GO

ALTER TABLE CasoDeUso WITH CHECK
ADD CONSTRAINT FK_CasoDeUso_proyecto FOREIGN KEY(proyecto_id)
REFERENCES Proyecto(id)
GO

ALTER TABLE ResultadoTest WITH CHECK
ADD CONSTRAINT FK_ResultadoTest_estado FOREIGN KEY(estado_id)
REFERENCES Estado(id)
GO

ALTER TABLE ResultadoTest WITH CHECK
ADD CONSTRAINT FK_ResultadoTest_test FOREIGN KEY(test_id)
REFERENCES Test(id)
GO

ALTER TABLE Usuario WITH CHECK
ADD CONSTRAINT FK_Usuario_rol FOREIGN KEY(rol_id)
REFERENCES Rol(id)
GO

ALTER TABLE PermisoXRol WITH CHECK
ADD CONSTRAINT FK_PermisoXRol_permiso FOREIGN KEY(permiso_id)
REFERENCES Permiso(id)
GO

ALTER TABLE PermisoXRol WITH CHECK
ADD CONSTRAINT FK_PermisoXRol_rol FOREIGN KEY(rol_id)
REFERENCES Rol(id)
GO

ALTER TABLE Permiso WITH CHECK
ADD CONSTRAINT FK_Permiso_formulario FOREIGN KEY(formulario_id)
REFERENCES Formulario(id)
GO
